The invention relates to computer systems, and more particularly to a method and mechanism for accessing past versions of data in a database system.
In database systems, a “transaction” normally refers to an atomic set of operations performed against a database. The transaction may access, create, modify, or delete database data or database metadata while it is being processed. A “commit” occurs when the transaction has completed its processing and any changes to the database by the transaction are ready to be “permanently” implemented in the database system. Because the transaction is atomic, all actions taken by the transaction must be committed at the same time.
In conventional systems, a committed transaction comprises a set of one or more permanent changes that are made against a set of records in a database system. Since the committed changes for a transaction are normally permanent, the transactions themselves are not normally reversible. Therefore, most database systems do not have an easily accessible way to access and view past versions of a data item.
Some database systems allow an archived version of a database system to be restored to a particular point in time, usually by restoring the entire database as of the time that the “back up” archive was made. However, this approach restores an entire database, even though only a small set of one or more transactions may need to be undone or compensated for to correct a data error made by the transactions. This is a highly inefficient approach if only a relatively small number of changes or transactions must be corrected.
One solution that has been provided is to undo, reverse, or compensate for a committed transaction in a database system, as described in co-pending U.S. application Ser. No. 10/364,065, filed on Feb. 10, 2003, which is hereby incorporated by reference in its entirety. In addition a solution has been proposed for identifying the one or more transactions that have modified or created an object, such as a row of data, in a database system, as described in co-pending U.S. application Ser. No. 10/364,209, filed on Feb. 10, 2003, which is hereby incorporated by reference in its entirety.
Embodiments of the present invention provide an improved interface, system, method, and mechanism for accessing past versions of a data item. In particular, embodiments of the invention provide a method, system, and medium for obtaining versions of data over a time interval.
In one embodiment, a mechanism is provided to allow specification of a new table decoration clause to obtain one or more past version of one or more data items. The version query provides for a direct interface to get multiple versions of the row(s). In addition, information about the past versions can be retrieved and displayed along with the past versions. Such information could include start time/SCN values, end time/SCN values, transaction identifier for the transaction that produces the version, and operation that resulted in the version.
Another embodiment of the invention includes algorithms, mechanisms, and processes that are employed to implement a query upon past versions. In one embodiment, the version query can be defined as either “rowid”-based access or non-rowid based access. With rowid based access, a given rowid is used to identify the data for which past versions are desired. Even within these two broad categories, there may be subcategories of rowid-based or non-rowid-based access to past versions of data. For example, with rowid-based access, the rowid may be provided by a user or from an index. With non-rowid-based access, there may be access initiated by identification of either a key, range of keys, block or from an entire table.
Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims.